package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.Absent;
import com.hlw.hr_man.Model.Overtime;
import com.hlw.hr_man.Model.SalaryModel;
import com.hlw.hr_man.dao.SalaryDao;
import com.hlw.hr_man.util.JDBCUtil;
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

public class SalaryDaoImpl implements SalaryDao{

	//得到总个数
	@Override
	public int getAllCout() {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sal_id) scount from hr_salary");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}
	
	//所有工资配置
	@Override
	public List<SalaryModel> getAllSalary(int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select sal_id,e_number,e_name,sal_base,sal_bonus,sal_fine,sal_date from hr_employee,hr_salary where hr_employee.e_id = hr_salary.e_id");
		sql.append(" limit ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				SalaryModel salary = new SalaryModel();
				salary.setSal_id(rs.getInt("sal_id"));
				salary.setE_number(rs.getString("e_number"));
				salary.setE_name(rs.getString("e_name"));
				salary.setSal_base(rs.getDouble("sal_base"));
				salary.setSal_bonus(rs.getDouble("sal_bonus"));
				salary.setSal_fine(rs.getDouble("sal_fine"));
				salary.setSal_date(rs.getString("sal_date"));
				sals.add(salary);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}

	//添加员工工资配置
	@Override
	public int addSalary(SalaryModel sal) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select e_id from hr_employee where e_number = ? and e_name = ? ");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, sal.getE_number());
			pst1.setString(2, sal.getE_name());
			ResultSet rs1 = pst1.executeQuery();
			//判断该员工在员工表里是否存在
			if(rs1.next()){
				e_id = rs1.getInt("e_id");
				//存在就继续添加，不存在返回-1
				if(e_id > 0){
					StringBuffer sql2 = new StringBuffer("select e_id from hr_salary where sal_date = ?");
					PreparedStatement pst2 = con.prepareStatement(sql2.toString());
					pst2.setString(1, sal.getSal_date());
					ResultSet rs2 = pst2.executeQuery();
					//判断该员工这个月工资配置是否存在
					//存在就返回0，不存在就继续添加
					if(rs2.next()){
						 flag = 0;
					}else{
						StringBuffer sql3 = new StringBuffer("insert into hr_salary (e_id,sal_base,sal_bonus,sal_fine,sal_date) values(?,?,?,?,?)");
						PreparedStatement pst3 = con.prepareStatement(sql3.toString());
						pst3.setInt(1, e_id);
						pst3.setDouble(2, sal.getSal_base());
						pst3.setDouble(3, sal.getSal_bonus());
						pst3.setDouble(4, sal.getSal_fine());
						pst3.setString(5, sal.getSal_date());
						int temp = pst3.executeUpdate();
						if(temp > 0){
							 flag = 1;
						}
					}
				}
			}else{
				 flag = -1;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	//修改员工工资配置
	@Override
	public int updateSalary(SalaryModel sal) {
		// TODO Auto-generated method stub
		int flag = 0;
		int e_id = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql1 = new StringBuffer("select e_id from hr_employee where e_number = ?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1 , sal.getE_number());
			ResultSet rs1 = pst1.executeQuery();
			if(rs1.next()){
				e_id = rs1.getInt("e_id");
			}
			StringBuffer sql2 = new StringBuffer("update hr_salary set sal_base = ?,sal_bonus = ?,sal_fine = ? where e_id = ? and sal_date = ?");
			PreparedStatement pst2 = con.prepareStatement(sql2.toString());
			pst2.setDouble(1, sal.getSal_base());
			pst2.setDouble(2, sal.getSal_bonus());
			pst2.setDouble(3, sal.getSal_fine());
			pst2.setInt(4, e_id);
			pst2.setString(5, sal.getSal_date());
			int temp  = pst2.executeUpdate();
			if(temp > 0 ){
				flag = 1;
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return flag;
	}
	
	//按月查找工资条数总数
	@Override
	public int findSalaryCountByDate(String date) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) as scount from hr_salary where sal_date = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, date);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	//按月算出工资
	@Override
	public List<SalaryModel> findSalaryByDate(String date, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		int abcount = 0;//缺 勤次数
		int otcount = 0;//加班次数
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select hr_employee.e_id,e_number,e_name,dept_name,sal_base,sal_bonus,sal_fine,sal_date "
				+ "from hr_employee,hr_salary,hr_department  "
				+ "where hr_employee.e_id = hr_salary.e_id and hr_employee.dept_id = hr_department.dept_id  and sal_date like ? limit ?,?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, date + "%");
			pst1.setInt(2, start);
			pst1.setInt(3, pageSize);
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				e_id = rs1.getInt("e_id");
				SalaryModel sal = new SalaryModel();
				sal.setE_id(e_id);
				sal.setE_number(rs1.getString("e_number"));
				sal.setE_name(rs1.getString("e_name"));
				sal.setDept_name(rs1.getString("dept_name"));
				sal.setSal_base(rs1.getDouble("sal_base"));
				sal.setSal_bonus(rs1.getDouble("sal_bonus"));
				sal.setSal_fine(rs1.getDouble("sal_fine"));
				sal.setSal_date(rs1.getString("sal_date"));
				
				//得到缺勤次数
				StringBuffer sql2 = new StringBuffer("select count(e_id) abcount from hr_absent where e_id = ? and absent_starttime like ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setInt(1, e_id);
				pst2.setString(2, date + "%");
				ResultSet rs2 = pst2.executeQuery();
				if(rs2.next()){
					abcount = rs2.getInt("abcount");
				}
				//得到加班次数
				StringBuffer sql3 = new StringBuffer("select count(e_id) otcount from hr_overtime where e_id = ? and ot_starttime like ?");
				PreparedStatement pst3 = con.prepareStatement(sql3.toString());
				pst3.setInt(1, e_id);
				pst3.setString(2, date + "%");
				ResultSet rs3 = pst3.executeQuery();
				if(rs3.next()){
					otcount = rs3.getInt("otcount");
				}
				
				//计算出总的加班工资、惩罚金额、以及总额
				sal.setSal_overtime(sal.getSal_bonus()*otcount);
				sal.setSal_penalty(sal.getSal_fine()*abcount);
				sal.setSal_total(sal.getSal_base()+sal.getSal_overtime()-sal.getSal_penalty());
				sals.add(sal);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}
	
	//通过月份和搜索值找总数
	@Override
	public int findSalaryCountByDateAndValue(String date, String value) {
		// TODO Auto-generated method stub
		int scount = 0;
		int e_id = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql1 = new StringBuffer("select e_id  from hr_employee where e_number like ? or e_name like ?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, "%" + value +"%");
			pst1.setString(2, "%" + value + "%");
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				e_id = rs1.getInt("e_id");
				
				StringBuffer sql2 = new StringBuffer("select count(e_id) as scount from hr_salary where sal_date = ? and e_id = ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setString(1, date);
				pst2.setInt(2, e_id);
				ResultSet rs2 = pst2.executeQuery();
				while(rs2.next()){
					scount += rs2.getInt("scount");
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}
	
	//通过月份和搜索值查询
	@Override
	public List<SalaryModel> findSalaryByValue(String date,String value, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		int abcount = 0;//缺 勤次数
		int otcount = 0;//加班次数
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select hr_employee.e_id,e_number,e_name,dept_name,sal_base,sal_bonus,sal_fine,sal_date "
				+ "from hr_employee,hr_salary,hr_department   "
				+ "where hr_employee.e_id = hr_salary.e_id and hr_employee.dept_id = hr_department.dept_id and (e_name like ? OR e_number like ?) and sal_date like ? limit ?,?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, "%" + value + "%");
			pst1.setString(2, "%" + value + "%");
			pst1.setString(3, "%" + date + "%");
			pst1.setInt(4, start);
			pst1.setInt(5, pageSize);
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				e_id = rs1.getInt("e_id");
				SalaryModel sal = new SalaryModel();
				sal.setE_id(e_id);
				sal.setE_number(rs1.getString("e_number"));
				sal.setE_name(rs1.getString("e_name"));
				sal.setDept_name(rs1.getString("dept_name"));
				sal.setSal_base(rs1.getDouble("sal_base"));
				sal.setSal_bonus(rs1.getDouble("sal_bonus"));
				sal.setSal_fine(rs1.getDouble("sal_fine"));
				sal.setSal_date(rs1.getString("sal_date"));
				//得到缺勤次数
				StringBuffer sql2 = new StringBuffer("select count(e_id) abcount from hr_absent where e_id = ? and absent_starttime like ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setInt(1, e_id);
				pst2.setString(2, date + "%");
				ResultSet rs2 = pst2.executeQuery();
				if(rs2.next()){
					abcount = rs2.getInt("abcount");
				}
				System.out.println("abcount:" + abcount);
				//得到加班次数
				StringBuffer sql3 = new StringBuffer("select count(e_id) otcount from hr_overtime where e_id = ? and ot_starttime like ?");
				PreparedStatement pst3 = con.prepareStatement(sql3.toString());
				pst3.setInt(1, e_id);
				pst3.setString(2, date + "%");
				ResultSet rs3 = pst3.executeQuery();
				if(rs3.next()){
					otcount = rs3.getInt("otcount");
				}
				//计算出总的加班工资、惩罚金额、以及总额
				sal.setSal_overtime(sal.getSal_bonus()*otcount);
				sal.setSal_penalty(sal.getSal_fine()*abcount);
				sal.setSal_total(sal.getSal_base()+sal.getSal_overtime()-sal.getSal_penalty());
				sals.add(sal);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}
	
	//所有工资信息总数
	@Override
	public int findSalaryAllCount() {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) as scount from hr_salary");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}
	
	//所有员工工资信息
	@Override
	public List<SalaryModel> findAllSalary(int start,int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		int abcount = 0;//缺 勤次数
		int otcount = 0;//加班次数
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select hr_employee.e_id,e_number,e_name,dept_name,sal_base,sal_bonus,sal_fine,sal_date "
				+ "from hr_employee,hr_salary,hr_department  "
				+ "where hr_employee.e_id = hr_salary.e_id and hr_employee.dept_id = hr_department.dept_id  limit ?,?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setInt(1, start);
			pst1.setInt(2, pageSize);
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				e_id = rs1.getInt("e_id");
				SalaryModel sal = new SalaryModel();
				sal.setE_id(e_id);
				sal.setE_number(rs1.getString("e_number"));
				sal.setE_name(rs1.getString("e_name"));
				sal.setDept_name(rs1.getString("dept_name"));
				sal.setSal_base(rs1.getDouble("sal_base"));
				sal.setSal_bonus(rs1.getDouble("sal_bonus"));
				sal.setSal_fine(rs1.getDouble("sal_fine"));
				sal.setSal_date(rs1.getString("sal_date"));
				
				//得到缺勤次数
				StringBuffer sql2 = new StringBuffer("select count(e_id) abcount from hr_absent where e_id = ? and absent_starttime like ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setInt(1, e_id);
				pst2.setString(2, sal.getSal_date() + "%");
				ResultSet rs2 = pst2.executeQuery();
				if(rs2.next()){
					abcount = rs2.getInt("abcount");
				}
				//得到加班次数
				StringBuffer sql3 = new StringBuffer("select count(e_id) otcount from hr_overtime where e_id = ? and ot_starttime like ?");
				PreparedStatement pst3 = con.prepareStatement(sql3.toString());
				pst3.setInt(1, e_id);
				pst3.setString(2, sal.getSal_date() + "%");
				ResultSet rs3 = pst3.executeQuery();
				if(rs3.next()){
					otcount = rs3.getInt("otcount");
				}
				
				//计算出总的加班工资、惩罚金额、以及总额
				sal.setSal_overtime(sal.getSal_bonus()*otcount);
				sal.setSal_penalty(sal.getSal_fine()*abcount);
				sal.setSal_total(sal.getSal_base()+sal.getSal_overtime()-sal.getSal_penalty());
				sals.add(sal);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}

	//通过部门找到工资总数
	@Override
	public int findSalaryCountByDeptName(String deptname) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(hr_salary.e_id) as scount "
				+ "from hr_salary,hr_employee,hr_department "
				+ "where dept_name = ? and hr_department.dept_id = hr_employee.dept_id and hr_employee.e_id = hr_salary.e_id");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, deptname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scount;
	}

	//通过部门查找
	@Override
	public List<SalaryModel> findSalaryByDeptName(String deptname, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		int abcount = 0;//缺 勤次数
		int otcount = 0;//加班次数
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select hr_employee.e_id,e_number,e_name,dept_name,sal_base,sal_bonus,sal_fine,sal_date "
				+ "from hr_employee,hr_salary,hr_department  "
				+ "where hr_employee.e_id = hr_salary.e_id and hr_employee.dept_id = hr_department.dept_id  and dept_name =  ? limit ?,?");
		try {
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, deptname);
			pst1.setInt(2, start);
			pst1.setInt(3, pageSize);
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				e_id = rs1.getInt("e_id");
				SalaryModel sal = new SalaryModel();
				sal.setE_id(e_id);
				sal.setE_number(rs1.getString("e_number"));
				sal.setE_name(rs1.getString("e_name"));
				sal.setDept_name(rs1.getString("dept_name"));
				sal.setSal_base(rs1.getDouble("sal_base"));
				sal.setSal_bonus(rs1.getDouble("sal_bonus"));
				sal.setSal_fine(rs1.getDouble("sal_fine"));
				sal.setSal_date(rs1.getString("sal_date"));
				
				//得到缺勤次数
				StringBuffer sql2 = new StringBuffer("select count(e_id) abcount from hr_absent where e_id = ? and absent_starttime like ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setInt(1, e_id);
				pst2.setString(2, deptname);
				ResultSet rs2 = pst2.executeQuery();
				if(rs2.next()){
					abcount = rs2.getInt("abcount");
				}
				//得到加班次数
				StringBuffer sql3 = new StringBuffer("select count(e_id) otcount from hr_overtime where e_id = ? and ot_starttime like ?");
				PreparedStatement pst3 = con.prepareStatement(sql3.toString());
				pst3.setInt(1, e_id);
				pst3.setString(2, deptname);
				ResultSet rs3 = pst3.executeQuery();
				if(rs3.next()){
					otcount = rs3.getInt("otcount");
				}
				
				//计算出总的加班工资、惩罚金额、以及总额
				sal.setSal_overtime(sal.getSal_bonus()*otcount);
				sal.setSal_penalty(sal.getSal_fine()*abcount);
				sal.setSal_total(sal.getSal_base()+sal.getSal_overtime()-sal.getSal_penalty());
				sals.add(sal);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}

	//通过员工id和日期得到这个月该员工缺勤总次数
	@Override
	public int getSalaryAbsentCountByNoAndDate(int e_id, String sal_date) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(hr_absent.e_id) as scount from hr_absent "
				+ "where hr_absent.e_id =  ? and absent_starttime like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setString(2, sal_date + "%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}
	
	//通过员工id和日期得到这个月该员工缺勤详情
	@Override
	public List<Absent> getSalaryAbsentByNoAndDate(int e_id, String sal_date, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Absent> abts = new ArrayList<Absent>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_absent.absent_starttime,hr_absent.absent_endtime "
				+ "from hr_employee,hr_absent where hr_employee.e_id = hr_absent.e_id "
				+ "and hr_absent.e_id = ? and hr_absent.absent_starttime like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setString(2, sal_date + "%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Absent abt = new Absent();
				abt.setE_number(rs.getString("e_number"));
				abt.setE_name(rs.getString("e_name"));
				abt.setAbsent_starttime(rs.getString("absent_starttime"));
				abt.setAbsent_endtime(rs.getString("absent_endtime"));
				abts.add(abt);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return abts;
	}

	//通过员工id和日期得到这个月该员工加班总次数
	@Override
	public int getSalaryOverTimeCountByNoAndDate(int e_id, String sal_date) {
		// TODO Auto-generated method stub
		int scount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(hr_overtime.e_id) as scount from hr_overtime "
				+ "where hr_overtime.e_id =  ? and ot_starttime like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setString(2, sal_date + "%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}
	
	//通过员工id和日期得到这个月该员工加班详情
	@Override
	public List<Overtime> getSalaryOverTimeByNoAndDate(int e_id, String sal_date, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Overtime> ovts = new ArrayList<Overtime>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select hr_employee.e_number,hr_employee.e_name,hr_overtime.ot_starttime,hr_overtime.ot_endtime "
				+ "from hr_employee,hr_overtime where hr_employee.e_id = hr_overtime.e_id "
				+ "and hr_overtime.e_id = ? and hr_overtime.ot_starttime like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, e_id);
			pst.setString(2, sal_date + "%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Overtime ovt = new Overtime();
				ovt.setE_number(rs.getString("e_number"));
				ovt.setE_name(rs.getString("e_name"));
				ovt.setOt_starttime(rs.getString("ot_starttime"));
				ovt.setOt_endtime(rs.getString("ot_endtime"));
				ovts.add(ovt);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ovts;
	}

	
	
	
	
	/*
	//按月算出员工工资
	@Override
	public List<SalaryModel> findSalaryByDate(String date, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT absent.e_id,abcount,otcount,sal_base,sal_bonus,sal_fine,sal_date,e_number,e_name,"
				+ "(otcount*sal_bonus) as sal_overtime,(abcount*sal_fine) as sal_penalty,(otcount*sal_bonus+sal_base-abcount*sal_fine)AS total "
				+ "FROM (SELECT e_id , COUNT(e_id) AS abcount from hr_absent WHERE absent_starttime LIKE ? GROUP BY e_id) AS absent, "
				+ "(SELECT e_id , COUNT(e_id) AS otcount from hr_overtime WHERE ot_starttime LIKE ? GROUP BY e_id) AS overtime,hr_salary,hr_employee "
				+ "WHERE absent.e_id=overtime.e_id=hr_salary.e_id  and absent.e_id =  hr_employee.e_id limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, date + "%");
			pst.setString(2, date + "%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				SalaryModel sal = new SalaryModel();
				sal.setE_id(rs.getInt("e_id"));
				sal.setE_number(rs.getString("e_number"));
				sal.setE_name(rs.getString("e_name"));
				sal.setSal_base(rs.getDouble("sal_base"));
				sal.setSal_overtime(rs.getDouble("sal_overtime"));
				sal.setSal_penalty(rs.getDouble("sal_penalty"));
				sal.setSal_total(rs.getDouble("total"));
				sals.add(sal);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}
	*/
	
	
	/*
	//按月算出工资
	@Override
	public List<SalaryModel> findSalaryByDate(String date, int start, int pageSize) {
		// TODO Auto-generated method stub
		List<SalaryModel> sals = new ArrayList<SalaryModel>();
		Connection con = JDBCUtil.getConnection();
		int abcount = 0;//缺勤次数
		int otcount = 0;//加班次数
		int e_id = 0;
		StringBuffer sql1 = new StringBuffer("select e_id,count(e_id) abcount from hr_absent where  absent_starttime like ? GROUP BY e_id");
		try {
			//得到缺勤次数
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst1.setString(1, date + "%");
			ResultSet rs1 = pst1.executeQuery();
			while(rs1.next()){
				abcount = rs1.getInt("abcount");
				e_id = rs1.getInt("e_id");
				//得到加班次数
				StringBuffer sql2 = new StringBuffer("select count(e_id) otcount from hr_overtime where e_id = ? and ot_starttime like ?");
				PreparedStatement pst2 = con.prepareStatement(sql2.toString());
				pst2.setInt(1, e_id);
				pst2.setString(2, date + "%");
				ResultSet rs2 = pst2.executeQuery();
				while(rs2.next()){
					otcount = rs2.getInt("otcount");
					//得到相应的数据
					StringBuffer sql3 = new StringBuffer("select e_number,e_name,sal_base,sal_bonus,sal_fine,sal_date "
							+ "from hr_employee innew join hr_salary on hr_employee.e_id = hr_salary.e_id "
							+ "where hr_employee.e_id = ? and sal_date like ? limit ?,?");
					PreparedStatement pst3 = con.prepareStatement(sql3.toString());
					pst3.setInt(1, e_id);
					pst3.setString(2, date + "%");
					pst3.setInt(3, start);
					pst3.setInt(4, pageSize);
					ResultSet rs3 = pst3.executeQuery();
					while(rs3.next()){
						SalaryModel sal = new SalaryModel();
						sal.setE_number(rs3.getString("e_number"));
						sal.setE_name(rs3.getString("e_name"));
						sal.setSal_base(rs3.getDouble("sal_base"));
						sal.setSal_bonus(rs3.getDouble("sal_bonus"));
						sal.setSal_fine(rs3.getDouble("sal_fine"));
						sal.setSal_date(rs3.getString("sal_date"));
						sal.setSal_overtime(sal.getSal_bonus()*otcount);
						sal.setSal_penalty(sal.getSal_fine()*abcount);
						sal.setSal_total(sal.getSal_base()+sal.getSal_overtime()-sal.getSal_penalty());
						sals.add(sal);
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sals;
	}
	*/
	

}
